USE [BS5]
GO
/****** Object:  View [dbo].[vw_VouchersDetail]    Script Date: 12/21/2016 12:45:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_VouchersDetail]
AS
SELECT     dbo.Voucher.VoucherID, dbo.Voucher.VoucherNo, dbo.Voucher.VoucherDate, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, dbo.SessionInfo.SessionFrom, 
                      dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.SenderID, 
                      Accounts_1.AccountTitle AS SenderName, dbo.Voucher.ReceiverID, Accounts_2.AccountTitle AS ReceiverName, dbo.Voucher.StationFromID, 
                      dbo.Station.StationName AS StationFromName, dbo.Station.StationNameUrdu AS StationFromNameUrdu, dbo.Voucher.StationToID, 
                      Station_1.StationName AS StationToName, Station_1.StationNameUrdu AS StationToNameUrdu, dbo.Voucher.VehicleID, Accounts_3.AccountTitle AS VehicleName, 
                      dbo.Voucher.BillityNo, dbo.Voucher.TotalAmount, dbo.Voucher.Loading, dbo.Voucher.GodownIn, dbo.Voucher.MiscIn, dbo.Voucher.GrossAmount, 
                      dbo.Voucher.UnLoading, dbo.Voucher.GodownOut, dbo.Voucher.MiscOut, dbo.Voucher.NetAmount, dbo.Voucher.Expense, dbo.Voucher.Discount, 
                      ISNULL(dbo.Voucher.CustomerName, '') AS CustomerName, dbo.VoucherDetails.VoucherDetailID, dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, 
                      dbo.Accounts.ParentAccount, dbo.VoucherDetails.DrOrCr, dbo.VoucherDetails.Amount, dbo.VoucherDetails.Amount AS Debit, 0 AS Credit, dbo.VoucherDetails.Remarks, 
                      dbo.vw_Items.GroupID, dbo.vw_Items.GroupCode, dbo.vw_Items.GroupName, dbo.vw_Items.GroupNameUrdu, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, 
                      dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.InUnit, dbo.VoucherDetails.Notes, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Rate, 
                      dbo.VoucherDetails.Weight, dbo.VoucherDetails.Qty AS InQty, 0 AS OutQty, dbo.VoucherDetails.Weight AS InWeight, 0 AS OutWeight
FROM         dbo.Accounts AS Accounts_3 RIGHT OUTER JOIN
                      dbo.Voucher ON Accounts_3.AccountNo = dbo.Voucher.VehicleID LEFT OUTER JOIN
                      dbo.Station AS Station_1 ON dbo.Voucher.StationToID = Station_1.StationID LEFT OUTER JOIN
                      dbo.Station ON dbo.Voucher.StationFromID = dbo.Station.StationID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_2 ON dbo.Voucher.ReceiverID = Accounts_2.AccountNo LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.Voucher.SenderID = Accounts_1.AccountNo RIGHT OUTER JOIN
                      dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo ON dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
WHERE     (dbo.VoucherDetails.DrOrCr = N'Dr')
UNION ALL
SELECT     Voucher_1.VoucherID, Voucher_1.VoucherNo, Voucher_1.VoucherDate, Voucher_1.SessionID, SessionInfo_1.SessionTitle, SessionInfo_1.SessionFrom, 
                      SessionInfo_1.SessionTo, SessionInfo_1.CurrentSession, Voucher_1.Remarks AS MasterRemarks, Voucher_1.SenderID, Accounts_1.AccountTitle AS SenderName, 
                      Voucher_1.ReceiverID, Accounts_2.AccountTitle AS ReceiverName, Voucher_1.StationFromID, Station_2.StationName AS StationFromName, 
                      Station_2.StationNameUrdu AS StationFromNameUrdu, Voucher_1.StationToID, Station_1.StationName AS StationToName, 
                      Station_1.StationNameUrdu AS StationToNameUrdu, Voucher_1.VehicleID, Accounts_3.AccountTitle AS VehicleName, Voucher_1.BillityNo, Voucher_1.TotalAmount, 
                      Voucher_1.Loading, Voucher_1.GodownIn, Voucher_1.MiscIn, Voucher_1.GrossAmount, Voucher_1.UnLoading, Voucher_1.GodownOut, Voucher_1.MiscOut, 
                      Voucher_1.NetAmount, Voucher_1.Expense, Voucher_1.Discount, ISNULL(Voucher_1.CustomerName, '') AS CustomerName, VoucherDetails_1.VoucherDetailID, 
                      VoucherDetails_1.AccountNo, Accounts_4.AccountTitle, Accounts_4.ParentAccount, VoucherDetails_1.DrOrCr, VoucherDetails_1.Amount, 0 AS Debit, 
                      VoucherDetails_1.Amount AS Credit, VoucherDetails_1.Remarks, vw_Items_1.GroupID, vw_Items_1.GroupCode, vw_Items_1.GroupName, 
                      vw_Items_1.GroupNameUrdu, VoucherDetails_1.ItemID, vw_Items_1.ItemCode, vw_Items_1.ItemName, vw_Items_1.ItemNameUrdu, vw_Items_1.InUnit, 
                      VoucherDetails_1.Notes, VoucherDetails_1.Qty, VoucherDetails_1.Rate, VoucherDetails_1.Weight, 0 AS InQty, VoucherDetails_1.Qty AS OutQty, 0 AS InWeight, 
                      VoucherDetails_1.Weight AS OutWeight
FROM         dbo.Accounts AS Accounts_3 RIGHT OUTER JOIN
                      dbo.Voucher AS Voucher_1 ON Accounts_3.AccountNo = Voucher_1.VehicleID LEFT OUTER JOIN
                      dbo.Station AS Station_1 ON Voucher_1.StationToID = Station_1.StationID LEFT OUTER JOIN
                      dbo.Station AS Station_2 ON Voucher_1.StationFromID = Station_2.StationID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_2 ON Voucher_1.ReceiverID = Accounts_2.AccountNo LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON Voucher_1.SenderID = Accounts_1.AccountNo RIGHT OUTER JOIN
                      dbo.VoucherDetails AS VoucherDetails_1 LEFT OUTER JOIN
                      dbo.vw_Items AS vw_Items_1 ON VoucherDetails_1.ItemID = vw_Items_1.ItemID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_4 ON VoucherDetails_1.AccountNo = Accounts_4.AccountNo ON Voucher_1.VoucherID = VoucherDetails_1.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo AS SessionInfo_1 ON Voucher_1.SessionID = SessionInfo_1.SessionID
WHERE     (VoucherDetails_1.DrOrCr = N'Cr')

------------------------------------------------------------------------------------------------------